﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace Assignment_9
{
    class ConnectDB
    {
        private static SqlConnection conn;
        private static SqlDataAdapter da;
        private static DataSet ds;
        private static DataView dv;

        public ConnectDB()
        {
            try
            {
                conn = new SqlConnection(Properties.Resources.sCN);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
            
        }

        public void loadGrid(DataGridView dgv)
        {
            try
            {
                da = new SqlDataAdapter("SELECT * FROM tbPatient", conn);
                new SqlCommandBuilder(da);
                ds = new DataSet();

                da.FillSchema(ds, SchemaType.Mapped);
                da.Fill(ds);
                dv = ds.Tables[0].DefaultView;

                dgv.DataSource = dv;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void loadComboboxSort(ComboBox cbo)
        {
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                cbo.Items.Add(ds.Tables[0].Columns[i].ColumnName);
            }
        }

        public void Search(string name)
        {
            string dk = String.Format("PatientName like '%{0}%'", name);
            dv.RowFilter = dk;
        }

        public void Sort(string sortBy)
        {
            dv.Sort = sortBy;
        }

        public Boolean Insert(List<object> lst)
        {
            try
            {
                DataRow row = ds.Tables[0].NewRow();
                for (int i = 0; i < lst.Count; i++)
                {
                    row[i] = lst[i];
                }

                ds.Tables[0].Rows.Add(row);
                da.Update(ds);
                ds.AcceptChanges();

                MessageBox.Show("Add new Patient Ok!!!");
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public void Update()
        {
            try
            {
                da.Update(ds);
                ds.AcceptChanges();
                MessageBox.Show("Update ok");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void Delete(int index)
        {
            ds.Tables[0].Rows[index].Delete();
            da.Update(ds);
            ds.AcceptChanges();
        }
    }
}
